﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Weight.Base
{
    public class SqlHelper
    {
        private readonly string connStr = ConfigurationManager.ConnectionStrings["SqlServerString"].ToString();
        private SqlConnection conn;

        private string _dataBaseName;
        public string DataBaseName { get { return _dataBaseName; } }
        public SqlHelper()
        {
            _dataBaseName = connStr.Split(';')?.ToList().FirstOrDefault(q => q.Contains("database"))?.Split('=')?[1];
            conn = new SqlConnection(connStr);
        }

        public DataTable GetData(string sql)
        {
            if (conn != null)
            {
                DataSet ds = new DataSet();
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    conn.Close();
                    return ds.Tables[0];
                }
                catch { }
            }
            return null;
        }

        public int SetData(string sql)
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                try
                {
                    conn.Open();
                    int rows = cmd.ExecuteNonQuery();
                    conn.Close();
                    return rows;
                }
                catch (Exception ex) { }
            }
            return 0;
        }

        public string GetSqlInsertObject<T>(T mode) where T : new()
        {
            string result = "";
            Type type = mode.GetType();
            var tablename = type.Name;
            List<string> fields = new List<string>();
            List<string> values = new List<string>();

            foreach (var item in type.GetProperties())
            {
                fields.Add(item.Name);
                values.Add(item.GetValue(mode).ToString());
            }
            result = string.Format("insert into {0} ({1}) values ('{2}')", tablename, string.Join(",", fields), string.Join("','", values));
            return result;
        }

        public string GetSqlInsertObject<T>(List<T> modeList) where T : new()
        {
            string result = "";
            Type type = typeof(T);
            var tablename = type.Name;
            List<string> fields = new List<string>();
            List<List<string>> values = new List<List<string>>();
            foreach (var item in type.GetProperties())
                fields.Add(item.Name);
            if (modeList != null && modeList.Count > 0)
            {
                foreach (var mode in modeList)
                {
                    List<string> value = new List<string>();
                    foreach (var property in type.GetProperties())
                        value.Add(property.GetValue(mode).ToString());
                    values.Add(value);
                }
                result = string.Format("insert into {0} ({1}) values ", tablename, string.Join("','", fields));
                List<string> singleValue = new List<string>();
                foreach (var value in values)
                {
                    singleValue.Add(string.Format("('{0}')", string.Join("','", value)));
                }
                result += string.Join(",", singleValue);
            }
            return result;
        }

        public DataTable GetTableMessage(string tableName)
        {
            string sql = "SELECT " +
                         "字段名 = CONVERT(VARCHAR(100), a.name), " +
                         "表名 = CONVERT(VARCHAR(50), d.name), " +
                         "类型 = CONVERT(VARCHAR(50), b.name), " +
                         //"库名 = 'ServerModeXpoDemo', " +
                         "说明 = CONVERT( " +

                         "VARCHAR(50), " +
                         "isnull(g.[value], '')) " +
                         "FROM " +
                         "dbo.syscolumns a " +
                         "LEFT JOIN dbo.systypes b ON a.xusertype = b.xusertype " +
                         "INNER JOIN dbo.sysobjects d ON a.id = d.id " +
                         "AND d.xtype = 'U' " +
                         "AND d.name <> 'dtproperties' " +
                         "LEFT JOIN dbo.syscomments e ON a.cdefault = e.id " +
                         "LEFT JOIN sys.extended_properties g ON a.id = g.major_id " +
                         "AND a.colid = g.minor_id " +
                         "LEFT JOIN sys.extended_properties f ON d.id = f.major_id " +
                         "AND f.minor_id = 0 " +
                         "WHERE " +
                         "d.name = '" + tableName + "'";
            if (conn != null)
            {
                DataSet ds = new DataSet();
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    conn.Close();
                    return ds.Tables[0];
                }
                catch (Exception ex) { }
            }
            return null;

        }
    }
}
